﻿using DataAccess.DTO;
using DataAccess.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYD.DataAccess;
using XYD.Framework.Data;
using XYD.Framework.Data.Dapper;
namespace DataAccess.DAL
{

    public class ProductEffectDAL : BaseDAL
    {

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="dt"></param>
        public static void BulkToDB(DataTable dt, string tableName)
        {
            SqlConnection sqlConn = new SqlConnection(ConnectionString);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = dt.Rows.Count;
            try
            {
                sqlConn.Open();

                if (dt != null && dt.Rows.Count != 0)
                    bulkCopy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
                if (bulkCopy != null)
                    bulkCopy.Close();
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="Str"></param>
        /// <returns></returns>
        public static int Delte(string Str)
        {
            return DBHelper.ExecuteSql(Str);
        }
      
        /// <summary>
        /// 推广收入分析
        /// </summary>
        /// <param name="Where"></param>
        /// <param name="type">1=预估，2=结算</param>
        /// <returns></returns>
        public List<ProductEffectDTO> GetPayAnalysis(string strWhere, int type, string order,int promoter_id)
        {

            string tableName = "ProductEffect_" + promoter_id;

            string SQL = "";
            if (string.IsNullOrEmpty(strWhere))
            {
                strWhere = " where  1=1 ";
            }


            SQL = type == 1 ? " select r.*,au.AlipayID,au.AliName  from ( select sum(Income* (rate/100.00))as Income,UserName,ChildIncome=(select sum(income* (rate_i/100.00)) from [" + tableName + "] p1    where p1.username_i=p.username {0}),LowestIncome=(select sum(income* (rate_ii/100.00)) from [" + tableName + "] p2    where p2.username_ii=p.username {0}),SumIncome=0 from [" + tableName + "] p where 1=1  {0}   group by username  ) r left join [dbo].[AgencyUsers] au on r.UserName=au.UserName" : "select r.*,au.AlipayID,au.AliName  from (select sum(Estimate* (rate/100.00))as Income,UserName,ChildIncome=(select sum(Estimate* (rate_i/100.00)) from [" + tableName + "] p1    where p1.username_i=p.username {0}),LowestIncome=(select sum(Estimate* (rate_ii/100.00)) from [" + tableName + "] p2    where p2.username_ii=p.username {0}),SumIncome=0 from [" + tableName + "] p where 1=1  {0}   group by username  ) r left join [dbo].[AgencyUsers] au on r.UserName=au.UserName";
            SQL = string.Format(SQL, strWhere) + order;

            return CONN_TopicSharing.SQLQuery<ProductEffectDTO>(SQL, new { }).ToList();
        }
        public List<UserCratDTO> GetUserCrat(string strWhere, int promoter_id)
        {

            string tableName = "ProductEffect_" + promoter_id;
            string SQL = "";
            if (string.IsNullOrEmpty(strWhere))
            {
                strWhere = " where  1=1 ";
            }

            SQL = string.Format("select CreateDate, sum(ClickNum)ClickNum,sum(PayNum)PayNum,sum(Estimate)Estimate,sum(Income)Income from [" + tableName + "] {0}  group by CreateDate order by CreateDate asc", strWhere);

            return CONN_TopicSharing.SQLQuery<UserCratDTO>(SQL, new { }).ToList();
        }

        public List<ProductEffect> ListPage(int promoter_id, string strWhere, string orderbys, int pagesize, int pageindex, out int count,object param=null)
        {
            string tableName = "ProductEffect_" + promoter_id;

            List<ProductEffect> ProductEffects = new List<ProductEffect>();
            if (string.IsNullOrEmpty(strWhere))
            {
                strWhere = " 1=1 ";
            }

            if (string.IsNullOrEmpty(orderbys))
                orderbys = "id";

            ProductEffects = CONN_TopicSharing.SQLPager<ProductEffect>("*", tableName, strWhere, "", "", orderbys, pagesize, pageindex, out count, param).ToList();

            return ProductEffects;
        }




        /// <summary>
        /// 按天统计收益
        /// </summary>
        /// <param name="promoter_id"></param>
        /// <param name="Date"></param>
        /// <returns></returns>
        public List<ProductEffect> Statistics_List_M(int promoter_id,string Date)
        {
            string tableName = "ProductEffect_" + promoter_id;

            string sql = " select CreateDate,sum(estimate)estimate,sum(income)income from [dbo].[" + tableName + "] where CreateDate>='" + Date + "' group by CreateDate ";

            List<ProductEffect> ProductEffects = new List<ProductEffect>();

            ProductEffects = CONN_TopicSharing.SQLQuery<ProductEffect>(sql, null).ToList();

            return ProductEffects;
        }
       

       



        /// <summary>
        /// 获取近期推广业绩
        /// </summary>
        /// <param name="promoter_id"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public List<ProductEffect_RecentDTO> GetRecentInfo(string promoter_id, string username)
        {
            string tableName = "ProductEffect_" + promoter_id;

            string yesterdayStr = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
            string current_monthStr = DateTime.Now.ToString("yyyy-MM-01");
            StringBuilder sql = new StringBuilder();
            string last_monthStr = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-01");
            //昨日佣金
            sql.Append("select 'yesterday_income'type, sum(clicknum) clicknum,sum(paynum)paynum,sum(estimate)estimate,sum(income) income from [" + tableName + "] where username=@username  and createdate =@yesterdayStr ");
            sql.Append("  union all ");
            //本月佣金
            sql.Append("select 'current_month_income' type,sum(clicknum) clicknum,sum(paynum)paynum,sum(estimate)estimate,sum(income) income from [" + tableName + "] where username=@username  and createdate >@current_monthStr  ");
            sql.Append(" union all ");

            //上月佣金
            sql.Append(" select 'last_month_income'type,sum(clicknum) clicknum,sum(paynum)paynum,sum(estimate)estimate,sum(income) income from [" + tableName + "] where username=@username  and createdate >=@last_monthStr and createdate<@current_monthStr   ");




            List<ProductEffect_RecentDTO> list = new List<ProductEffect_RecentDTO>();

            list = CONN_TopicSharing.SQLQuery<ProductEffect_RecentDTO>(sql.ToString(), new { username = username, yesterdayStr = yesterdayStr, current_monthStr = current_monthStr, last_monthStr = last_monthStr, promoter_id = promoter_id }).ToList();


            return list;
        }


        /// <summary>
        /// 获取代理排行榜
        /// </summary>
        /// <param name="promoter_id"></param>
        /// <returns></returns>
        public List<RankingList> GetRankingList(int promoter_id)
        {

            string tableName = "ProductEffect_" + promoter_id;

            string sql = @" select top 10 au.nickname,au.head_img,p.* from [AgencyUsers] au join  (select username,sum(estimate)estimate  from  ["+tableName+@"]  group by username
 ) p 
  on p.username=au.username
  order by estimate desc";
            List<RankingList> list = new List<RankingList>();
            list = CONN_TopicSharing.SQLQuery<RankingList>(sql, null).ToList();
            return list;

            
        }
        


    }
}
